<?php
namespace app\index\controller;
use app\common\controller\Base;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;
use think\Db;
use think\Env;


class Office extends Base{

	public function hello(){
		return '12311111111111111';
	}

	public function downExcel(){
		$data = $this->request->param();
		// return $data;
		$this->redirect('http://localhost/myphp8/public/index/office/excelone',302);
		// if ($data['typee'] == 3) {
		// 	$this->redirect('http://localhost/myphp8/public/index/office/excelone',302);
		// };
	}
	
	public function cpcpDb(){
		$fileName = '乘联会周报数据库' . date('YmdHis') .'.xlsx';

		$result = Db::query('select * from my_data');
		$result = Db::query("select e.yy as '年',e.mm as '月',e.ww as '周',c.name as '厂家',b.name as '品牌',a.name as '车型',d.wholesale as '批发',d.retail as '零售' "
				. "from car_model as a "
				. "inner join car_model as b on a.pid = b.id "
				. "inner join car_model as c on b.pid = c.id "
				. "inner join my_data as d on a.id = d.model_id "
				. "left join my_date as e on d.date_id = e.date_id");
		header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
		header("Content-Disposition:attachment;filename=" . iconv("utf-8", "GB2312//TRANSLIT", $fileName));
		header('Cache-Control: max-age=0');//禁止缓存
		// var_dump(count($result[0]));
		// header('Content-Length: 5555'); //设置内容长度

		$cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');

		$savePath = 'php://output';

		$spreadsheet = new Spreadsheet();
		$sheet = $spreadsheet->getActiveSheet();
		$sheet->setTitle("Sheet1");

		$colNum = count($result[0]);
		// for($i=0;$i<$colNum;$i++){
	 //      $sheet->setCellValue($cellName[$i].'2', $expCellName[$i][1]);
	 //    }

		// Set cell A1 with a string value
		$sheet->setCellValue('A1', '年');
		$sheet->setCellValue('B1', '月');
		$sheet->setCellValue('C1', '周');
		$sheet->setCellValue('D1', '厂家');
		$sheet->setCellValue('E1', '品牌');
		$sheet->setCellValue('F1', '车型');
		$sheet->setCellValue('G1', '批发');
		$sheet->setCellValue('H1', '零售');
		$sheet->fromArray($result,0.6180339314159,'A2');

		// $writer = new Xlsx($spreadsheet);
		// $writer->save($fileName);
		// return ['status'=>1,'message'=>'表格下载完成'];

		$objWriter = IOFactory::createWriter($spreadsheet, 'Xlsx');
		$objWriter->save($savePath);

		$spreadsheet->disconnectWorksheets();
		unset($spreadsheet);
		ob_end_flush();

	}

	public function excelone(){

		$fileName = '乘联会周报' . date('YmdHis') .'.xlsx';

		$fileName = !empty($fileName) ? $fileName : (date('YmdHis') . '.xlsx');
		header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
		header("Content-Disposition:attachment;filename=" . iconv("utf-8", "GB2312//TRANSLIT", $fileName));
		header('Cache-Control: max-age=0');//禁止缓存
		$savePath = 'php://output';

		$spreadsheet = new Spreadsheet();
		$sheet = $spreadsheet->getActiveSheet();
		$sheet->setTitle("Sheet1");
		$sheet->setCellValue('C1', 'Hello World !');

		// Set cell A1 with a string value
		$sheet->setCellValue('A1', 'PhpSpreadsheet');

		// Set cell A2 with a numeric value
		$sheet->setCellValue('A2', 12345.6789);

		// Set cell A3 with a boolean value
		$sheet->setCellValue('A3', TRUE);

		$sheet->setCellValue('A6', date('YmdHis'));
		// Set cell A4 with a formula
		$sheet->setCellValue(
		    'A4',
		    '=IF(A3, CONCATENATE(A1, " ", A2), CONCATENATE(A2, " ", A1))'
		);

		$arrayData = [
		    [NULL, 2010, 2011, 2012],
		    ['Q1',   12,   15,   21],
		    ['Q2',   56,   73,   86],
		    ['Q3',   52,   61,   69],
		    ['Q4',   30,   32,    0],
		];
		$sheet->fromArray(
		        $arrayData,  // The data to set
		        NULL,        // Array values with this value will not be set
		        'C3'         // Top left coordinate of the worksheet range where
		    //    we want to set these values (default is A1)
		    );

		// $writer = new Xlsx($spreadsheet);
		// $writer->save($fileName);
		// return ['status'=>1,'message'=>'表格下载完成'];

		$objWriter = IOFactory::createWriter($spreadsheet, 'Xlsx');
		$objWriter->save($savePath);

		$spreadsheet->disconnectWorksheets();
		unset($spreadsheet);
		ob_end_flush();
		// $this->redirect('http://localhost/myphp8/public/' + $fileName,302);

	}

	public function upload()
    {
        // 获取表单上传文件  
        $file = request()->file('myfile');
		var_dump($file["filename"]);

        if(empty($file)) {  
            $this->error('请选择上传文件');  
        }  
        // 移动到框架应用根目录/public/uploads/ 目录下  
        $info = $file->move(ROOT_PATH.'public'.DS.'uploads'); 
        //如果不清楚文件上传的具体键名，可以直接打印$info来查看  
        //获取文件（文件名），$info->getFilename()  ***********不同之处，笔记笔记哦
        //获取文件（日期/文件名），$info->getSaveName()  **********不同之处，笔记笔记哦
        $filename = $info->getSaveName();  //在测试的时候也可以直接打印文件名称来查看 
        $filePath = Env::get('root_path').'public'.DIRECTORY_SEPARATOR.'uploads'.DIRECTORY_SEPARATOR.$filename;
        dump($filePath);
        // if($filename){
        //     $this->success('文件上传成功！');  
        // }else{  
        //     // 上传失败获取错误信息  
        //     $this->error($file->getError());  
        // }
        //获取文件后缀
        $suffix = $info->getExtension();
        //判断哪种类型
        if($suffix=="xlsx"){
            $reader = IOFactory::createReader('Xlsx');
        }else{
            $reader = IOFactory::createReader('Xls'); 
        }
        // $filePaths = 'C:/wamp64/www/myphp8/' + $filePath
        // $filePaths = './' + $filePath
        //载入excel文件
        // $excel = $reader->load(".\\" . "$filePath",$encode = 'utf-8');
        $excel = $reader->load("./public/uploads/20191007/995d7395fc9274eccea8b7e9b72b1fd5.xlsx",$encode = 'utf-8');
        //读取第一张表
        $sheet = $excel->getSheet(0);
        //获取总行数
        $row_num = $sheet->getHighestRow();
        //获取总列数
        $col_num = $sheet->getHighestColumn();
        $data = []; //数组形式获取表格数据

        for ($i=2; $i <=$row_num; $i++) { 
            // var_dump($sheet->getCell("A".$i)->getValue());exit;
            $data['A']   = $sheet->getCell("A".$i)->getValue();
            $data['B'] = $sheet->getCell("B".$i)->getValue();
            $data['C'] = $sheet->getCell("C".$i)->getValue();
            $data['D'] = $sheet->getCell("D".$i)->getValue();
            
            $data['status']     = 0;
        }

        dump($data);

        
    }

    public function asd(){
    	dump(ROOT_PATH);
    	
    }

    public function updd(){

    	// https://blog.csdn.net/qq1719448063/article/details/80844212
    	// 如果报ZipArchive::getFromName(): Invalid or uninitialized Zip object为excel后缀问题，转换成xlsx后缀
		// Could not open xxx for reading! File does not exist.为获取路径的问题
    	
    	$file = request()->file('myfile');//首先获取到文件的名称
		$info = $file->move(ROOT_PATH . 'public' . DS . 'uploads');
		//将文件移动上传至根目录/public/uploads/ 目录之下
		// $data = ROOT_PATH .DS . 'uploads' . DS . $info->getSaveName();
		$data = ROOT_PATH . 'public\uploads' . DS . $info->getSaveName();
		dump($info);
		echo "<hr>";
		dump($data);
		// dump(gettype($data));
		// dump(str_replace("/","\\",$data);
		echo "<hr>";
		$reader = IOFactory::createReader('Xlsx');
		$excel = $reader->load($data,$encode = 'utf-8');

		
		//读取第一张表
        $sheet = $excel->getSheet(0);
        //获取总行数
        $row_num = $sheet->getHighestRow();
        //获取总列数
        $col_num = $sheet->getHighestColumn();
        $data = []; //数组形式获取表格数据

        for ($i=2; $i <=$row_num; $i++) { 
            // var_dump($sheet->getCell("A".$i)->getValue());exit;
            $data['A'.$i]   = $sheet->getCell("A".$i)->getValue();
            $data['B'.$i] = $sheet->getCell("B".$i)->getValue();
        }

        dump($data);
    }

    public function readExcel(string $file, $index = false)
    {
        $spreadsheet = IOFactory::load($file);
        $sheetCount = $spreadsheet->getSheetCount();//获取文件中表格数量
        $sheetData = [];
        if (is_int($index) && $index < $sheetCount && $index >= 0) {
            $sheetData[] = $spreadsheet->getSheet($index)->toArray(null, true, true, true);
        }
        if ($sheetCount == 1) {
            $sheetData[] = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
        }
        for ($i = 0; $i < $sheetCount; $i++) {
            $sheetData[] = $spreadsheet->getSheet($i)->toArray(null, true, true, true);
        }
        return $sheetData;
    }

}





?>